﻿using XW.Data;
using XW.Utils;
using XW.Logging;
using System;
using System.Collections.Generic;
using System.Text;

namespace XW.Core
{
    /// <summary>
    ///  sql 指令封装
    /// </summary>
    public partial class SqlCmd
    {
        private ILog L = LogProvider.GetCurrentClassLogger();
        #region 属性
        private StringBuilder _sql = new StringBuilder();
        public StringBuilder Sql
        {
            get
            {
                if (_sql == null)
                {
                    _sql = new StringBuilder();
                }
                return _sql;
            }

            set
            {
                _sql = value;
            }
        }
        private Dictionary<string, object> _parameters = new Dictionary<string, object>();
        public Dictionary<string, object> Parameters
        {
            get
            {
                if (_parameters == null)
                {
                    _parameters = new Dictionary<string, object>();
                }
                return _parameters;
            }
            set { _parameters = value; }
        }
        private bool _isProceduce = false;
        public bool IsProceduce
        {
            get
            {
                return _isProceduce;
            }
            set
            {
                _isProceduce = value;
            }
        }
        #endregion

        public static SqlCmd CreateInserOrUpdate(string _tablName, object where, object obj)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.InserOrUpdate(_tablName, where, obj);
            return cmd;
        }

        public static SqlCmd CreateInserOrUpdate(string _tablName, object where, object addObj, object updateObj)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.InserOrUpdate(_tablName, where, addObj, updateObj);
            return cmd;
        }

        public static SqlCmd StaticUpdate(string _tablName, object where, object updateObj)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.Update(_tablName, where, updateObj);
            return cmd;
        }
        public static SqlCmd StaticUpdateWithUser(string _tablName, object where, object updateObj)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.UpdateWithUser(_tablName, where, updateObj);
            return cmd;
        }

        public static SqlCmd StaticInsert(string _tablName,   object addObj)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.Insert(_tablName, addObj);
            return cmd;
        }
        public static SqlCmd StaticInsertWithUser(string _tablName,  object addObj)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.InsertWithUser(_tablName, addObj);
            return cmd;
        }
        public static SqlCmd StaticInserWithUserIfNotExists(string _tablName, object where, object addObj) {
            SqlCmd cmd = new SqlCmd();
            cmd.InserWithUserIfNotExists(_tablName, where, addObj);
            return cmd;
        }
        public SqlCmd UpdateWithUser(string _tablName, object where, object obj)
        {
            var whereColumns = GetColumns(where);
            var dataColumns = GetColumns(obj); 
            IDictionary<string, object> updateDatas = new Dictionary<string, object>();
            foreach (var key in dataColumns.Keys)
            { 
                updateDatas.Add(key, dataColumns[key]);
            }
            updateDatas.Add("update_time", DateTime.Now);
            updateDatas.Add("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            
            return Update(_tablName, whereColumns,  updateDatas);
        }
        public static SqlCmd CreateInserOrUpdateWithUser(string _tablName, object where, object obj) {
            SqlCmd cmd = new SqlCmd();
            cmd.InserOrUpdateWithUser( _tablName,  where,  obj);
            return cmd;
        }

        public SqlCmd InsertWithUser(string _tablName, object obj)
        {
            var dataColumns = GetColumns(obj);
            IDictionary<string, object> addDatas = new Dictionary<string, object>(); 
            foreach (var key in dataColumns.Keys)
            {
                addDatas.Add(key, dataColumns[key]); 
            }
           
            addDatas.Add("update_time", DateTime.Now);
            addDatas.Add("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            addDatas.Add("create_time", DateTime.Now);
            addDatas.Add("create_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            return Insert(_tablName, addDatas);
        }

        public SqlCmd InserOrUpdateWithUser(string _tablName, object where, object obj)
        {
            var whereColumns = GetColumns(where);
            var dataColumns = GetColumns(obj);
            IDictionary<string, object> addDatas = new Dictionary<string, object>();
            IDictionary<string, object> updateDatas = new Dictionary<string, object>();
            foreach (var key in dataColumns.Keys)
            {
                addDatas.Add(key, dataColumns[key]);
                updateDatas.Add(key, dataColumns[key]);
            }
            updateDatas.Add("update_time", DateTime.Now);
            updateDatas.Add("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            addDatas.Add("update_time", DateTime.Now);
            addDatas.Add("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            addDatas.Add("create_time", DateTime.Now);
            addDatas.Add("create_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            return InserOrUpdate(_tablName,where, addDatas, updateDatas);
        }

        
        public SqlCmd InserWithUserIfNotExists(string _tablName, object where, object obj)
        {
            var whereColumns = GetColumns(where);
            var dataColumns = GetColumns(obj);
            IDictionary<string, object> addDatas = new Dictionary<string, object>(); 
            foreach (var key in dataColumns.Keys)
            {
                addDatas.Add(key, dataColumns[key]); 
            } 
            addDatas.Add("update_time", DateTime.Now);
            addDatas.Add("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            addDatas.Add("create_time", DateTime.Now);
            addDatas.Add("create_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            return InserIfNotExists(_tablName, where, addDatas);
        }
        public SqlCmd InserOrUpdate(string _tablName, object where, object obj)
        {
            var addDataColumns = GetColumns(obj);
            var whereColumns = GetColumns(where);

            var insertSql = "";
            var valuesSql = "";
            foreach (var column in addDataColumns)
            {
                if (insertSql.Length > 0)
                {
                    insertSql += ",";
                    valuesSql += ",";
                }

                insertSql += "[" + column.Key + "]";
                valuesSql += "@" + column.Key;
                this.AddParameter(column.Key, column.Value);
            }

            string whereSql = "";
            foreach (var column in whereColumns)
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";

                whereSql += string.Format("{0} = @{1}", "[" + column.Key + "]", column.Key);
                this.AddParameter(column.Key, column.Value);
            }

            var setSql = "";
            foreach (var column in addDataColumns)
            {
                if (whereColumns.ContainsKey(column.Key))
                {
                    continue;
                }
                if (setSql.Length > 0)
                    setSql += ", ";

                setSql += string.Format("{0} = @{1}", "[" + column.Key + "]", column.Key);
                this.AddParameter(column.Key, column.Value);
            }

            this.Sql.AppendFormat("IF EXISTS (SELECT 1 FROM {0} WHERE {1}) BEGIN ", _tablName, whereSql);
            this.Sql.AppendFormat(" UPDATE {0} set {1} where {2} END ", _tablName, setSql, whereSql);
            this.Sql.Append(" ELSE BEGIN ");
            this.Sql.AppendFormat(" insert into {0}({1}) values({2}) ", _tablName, insertSql, valuesSql);
            this.Sql.Append(" end ");

            return this;
        }

        public SqlCmd InserOrUpdate(string _tablName, object where, object addObj, object updateObj)
        {
            var addDataColumns = GetColumns(addObj);
            var updateColumns = GetColumns(updateObj);
            var whereColumns = GetColumns(where);

            var insertSql = "";
            var valuesSql = "";
            foreach (var column in addDataColumns)
            {
                if (insertSql.Length > 0)
                {
                    insertSql += ",";
                    valuesSql += ",";
                }

                insertSql += "[" + column.Key + "]";
                valuesSql += "@" + column.Key + "0";
                this.AddParameter(column.Key + "0", column.Value);
            }

            string whereSql = "";
            foreach (var column in whereColumns)
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";

                whereSql += string.Format("{0} = @{1}", "[" + column.Key + "]", column.Key + "1");
                this.AddParameter(column.Key + "1", column.Value);
            }

            var setSql = "";
            foreach (var column in updateColumns)
            {
                if (setSql.Length > 0)
                    setSql += ", ";

                setSql += string.Format("{0} = @{1}", "[" + column.Key + "]", column.Key + "2");
                this.AddParameter(column.Key + "2", column.Value);
            }

            this.Sql.AppendFormat("IF EXISTS (SELECT 1 FROM {0} WHERE {1}) BEGIN ", _tablName, whereSql);
            this.Sql.AppendFormat(" UPDATE {0} set {1} where {2} END ", _tablName, setSql, whereSql);
            this.Sql.Append(" ELSE BEGIN ");
            this.Sql.AppendFormat(" insert into {0}({1}) values({2}) ", _tablName, insertSql, valuesSql);
            this.Sql.Append(" end ");

            return this;
        }
        /// <summary>
        ///  如果不存在则插入
        /// </summary>
        /// <param name="_tablName">Name of the tabl.</param>
        /// <param name="where">The where.</param>
        /// <param name="addObj">The add object.</param>
        /// <returns></returns>
        public SqlCmd InserIfNotExists(string _tablName, object where, object addObj )
        {
            var addDataColumns = GetColumns(addObj); 
            var whereColumns = GetColumns(where);

            var insertSql = "";
            var valuesSql = "";
            foreach (var column in addDataColumns)
            {
                if (insertSql.Length > 0)
                {
                    insertSql += ",";
                    valuesSql += ",";
                }

                insertSql += "[" + column.Key + "]";
                valuesSql += "@" + column.Key + "0";
                this.AddParameter(column.Key + "0", column.Value);
            }

            string whereSql = "";
            foreach (var column in whereColumns)
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";

                whereSql += string.Format("{0} = @{1}", "[" + column.Key + "]", column.Key + "1");
                this.AddParameter(column.Key + "1", column.Value);
            }
 

            this.Sql.AppendFormat("IF Not EXISTS (SELECT 1 FROM {0} WHERE {1}) BEGIN ", _tablName, whereSql);
            this.Sql.AppendFormat(" insert into {0}({1}) values({2}) ", _tablName, insertSql, valuesSql); 
            this.Sql.Append(" end "); 
            return this;
        }
        public SqlCmd Insert(string _tablName,  object addObj)
        {
            var addDataColumns = GetColumns(addObj); 

            var insertSql = "";
            var valuesSql = "";
            foreach (var column in addDataColumns)
            {
                if (insertSql.Length > 0)
                {
                    insertSql += ",";
                    valuesSql += ",";
                }

                insertSql += "[" + column.Key + "]";
                valuesSql += "@" + column.Key;
                this.AddParameter(column.Key, column.Value);
            }
             
            this.Sql.AppendFormat(" insert into {0}({1}) values({2}) ", _tablName, insertSql, valuesSql); 

            return this;
        }
        /// <summary>
        /// 生成保存指令
        /// </summary>
        /// <param name="_tablName"></param>
        /// <param name="where"></param>
        /// <param name="updateObj"></param>
        /// <returns></returns>
        public SqlCmd Update(string _tablName, object where, object updateObj) {
             
            var updateColumns = GetColumns(updateObj);
            var whereColumns = GetColumns(where);
             

            string whereSql = "";
            foreach (var column in whereColumns)
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";

                whereSql += string.Format("{0} = @{1}0", "[" + column.Key + "]", column.Key);
                this.AddParameter(column.Key+"0", column.Value);
            }

            var setSql = "";
            foreach (var column in updateColumns)
            {
                //if (whereColumns.ContainsKey(column.Key))
                //{
                //    continue;
                //}
                if (setSql.Length > 0)
                    setSql += ", ";

                setSql += string.Format("{0} = @{1}1", "[" + column.Key + "]", column.Key);
                this.AddParameter(column.Key+"1", column.Value);
            }

            this.Sql.AppendFormat(" UPDATE {0} set {1} where {2}  ", _tablName, setSql, whereSql);

            return this;
        }
        public static SqlCmd StaticProceduce(string sql, object parameters)
        {
            SqlCmd cmd = new SqlCmd();
            cmd.Proceduce(sql, parameters);
            return cmd;
        }
        public SqlCmd Proceduce(string sql, object parameters)
        {
            this.IsProceduce = true;
            var parametersDic = GetColumns(parameters);

            foreach (var column in parametersDic)
            {

                this.AddParameter(column.Key, column.Value);
            }
            this.Sql.Append(sql);
            return this;
        }
        /// <summary>
        /// 生成删除指令
        /// </summary>
        /// <param name="_tablName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static SqlCmd StaticDelete(string _tablName, object where) {
            SqlCmd cmd = new SqlCmd();
            cmd.Delete(_tablName, where);
            return cmd;
        }

        /// <summary>
        /// 生成删除指令
        /// </summary>
        /// <param name="_tablName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public SqlCmd Delete(string _tablName, object where)
        { 
            var whereColumns = GetColumns(where);


            string whereSql = "";
            foreach (var column in whereColumns)
            {
                if (whereSql.Length > 0)
                    whereSql += " and ";

                whereSql += string.Format("{0} = @{1}", "[" + column.Key + "]", column.Key);
                this.AddParameter(column.Key, column.Value);
            } 
            this.Sql.AppendFormat(" Delete From {0}  where {1}  ", _tablName,  whereSql);

            return this;
        }

        public SqlCmd AddParameter(object obj)
        {
            var columns = GetColumns(obj);
            foreach (var k in columns.Keys)
            {
                AddParameter(k, columns[k]);
            }
            return this;
        }

        public SqlCmd Append(string s)
        {
            Sql.Append(" ");
            Sql.Append(s);
            Sql.Append(" ");
            return this;
        }
        public SqlCmd AppendFormat(string s, params object[] args)
        {
            Sql.Append(" ");
            Sql.AppendFormat(s,args);
            Sql.Append(" ");
            return this;
        }
        public SqlCmd AddParameter(string key, object obj)
        {
            if (!Parameters.ContainsKey(key))
            { 
                Parameters.Add(key, obj);
            }
            return this;
        }
        public IDictionary<string, object> GetColumns(object obj)
        {
            IDictionary<string, object> columns = obj is IDictionary<string, object> ? obj as IDictionary<string, object> : obj.GetPropertiesDic();

            return columns;
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public int Excute(IDbContext db)
        {

            var cmd = db.Sql(this.Sql.ToString());
            List<string> parameters = new List<string>();
            foreach (var k in this.Parameters.Keys)
            {
                var value = this.Parameters[k];
                if (value is byte[])
                {
                    cmd.Parameter(k, value, parameterType : DataTypes.Binary);
                }
                else if (value is string)
                {
                    cmd.Parameter(k, value, DataTypes.String);
                }
                else
                {
                   
                    cmd.Parameter(k, value);
                    parameters.Add(string.Format("{0}:{1}",k, value));
                }
               
            }
           
            var r = cmd.Execute();
            L.Debug("sql:{0},r:{1},paras:{2}",this.Sql.ToString(),r,string.Join(",", parameters));
            return r;
        }


    }
}
